package com.exonline.controller.courses;

import com.exonline.base.IBaseService;
import com.exonline.commons.dao.DaoImpl;
import com.exonline.commons.dao.IDao;
import com.exonline.entity.Courses;
import com.exonline.entity.Direction;
import com.exonline.entity.Subject;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 科目M层
 *
 * @author JJR
 * @date 2024/11/27
 */
public class CoursesService implements IBaseService<Courses> {
    IDao<Courses> coursesIDao = new DaoImpl<>();

    /**
     * @desc  教师端课程信息的查询
     * @param tid
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> tqueryById(String tid) throws SQLException {

        String sql = "SELECT t.id AS id, " +
                "       c.cover AS course_cover, "+
                "       c.id AS course_id, " +
                "       c.cname AS course_name, " +
                "       c.cprice AS course_price, " +
                "       c.cviews AS course_views, " +
                "       c.csales AS course_sales, " +
                "       c.cstatus AS course_status " +
                "FROM teacher t " +
                "JOIN userrole ur ON t.id = ur.tid " +
                "JOIN courses c ON ur.tid = c.tid " +
                "WHERE t.id =?";
        return coursesIDao.queryWithPagingAndConditions(sql, null, 1, 10, new MapListHandler(),tid);
    }

    /**
     * 根据指定的榜单类型进行相应榜单数据查询
     *
     * @param boardType 榜单类型，取值可以为 "best_selling"（畅销榜）或 "most_studied"（学习榜），用于区分要查询的具体榜单
     * @return 对应榜单课程信息列表（以Map形式表示每条课程记录）
     * @throws SQLException SQL执行异常
     */
    public List<Map<String, Object>> getBoardData(String boardType) throws SQLException {
        if ("best_selling".equals(boardType)) {
            String sql = "SELECT " +
                    "`c`.`id` AS `course_id`, " +
                    "`c`.`cname` AS `course_name`, " +
                    "`c`.`cprice` AS `course_price`, " +
                    "`c`.`csales` AS `sales_count`, " +
                    "`c`.`cintroduce` AS `courses_introduce`, " +
                    "`c`.`clevel` AS `course_level`, " +
                    "`t`.`tname` AS `teacher_name` " +
                    "FROM (" +
                    "`courses` `c` " +
                    "JOIN `teacher` `t` ON (`c`.`tid` = `t`.`id`)" +
                    ") " +
                    "ORDER BY `c`.`csales` DESC ";
            return coursesIDao.queryWithPagingAndConditions(sql, null, 1, 10, new MapListHandler());
        } else if ("most_studied".equals(boardType)) {
            String sql = "SELECT " +
                    "c.id AS course_id, " +
                    "c.cname AS course_name, " +
                    "c.cprice AS course_price, " +
                    "c.cintroduce AS courses_introduce, " +
                    "c.clevel AS course_level, " +
                    "COUNT(sc.sid) AS student_count " +
                    "FROM courses c " +
                    "LEFT JOIN studentcourses sc ON c.id = sc.cid " +
                    "LEFT JOIN userorder uo ON c.id = uo.cid AND sc.sid = uo.sid " +
                    "GROUP BY c.id, c.cname, c.cintroduce, c.clevel " +
                    "ORDER BY student_count DESC ";
            return coursesIDao.queryWithPagingAndConditions(sql, null, 1, 10, new MapListHandler());
        }

        return null;
    }

    /**
     * @desc  个人中心的学习记录查询
     * @param studentId
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> getIndividualCourses(String studentId) throws SQLException {
        String sql = "SELECT c.* " +
                "FROM studentcourses sc " +
                "JOIN student s ON sc.sid = s.id " +
                "JOIN courses c ON sc.cid = c.id " +
                "WHERE s.id =?";
        return coursesIDao.queryWithPagingAndConditions(sql, null, 1, 10, new MapListHandler(), studentId);
    }

    /**
     * 获取新课推荐的课程信息列表（以Map形式表示每条课程记录），
     * 当前按照查询最近一周内上传的课程来实现，可根据实际需求调整查询逻辑。
     *
     * @return 新课推荐课程信息列表
     * @throws SQLException SQL执行异常
     */
    public List<Direction> getNewCourses() throws SQLException {
//        String sql = "SELECT * " +
//                "FROM courses " +
//                "WHERE creleasetime >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 WEEK)";
        List<Direction> directions = coursesIDao.queryWithPagingAndConditions("SELECT DISTINCT cdirection AS directionName FROM newcourseview", null, 1, 8, new BeanListHandler<>(Direction.class));
        List<Subject> subjects = coursesIDao.queryWithPagingAndConditions("SELECT DISTINCT csubject AS subjectName FROM newcourseview", null, 1, 8, new BeanListHandler<>(Subject.class));
        List<Courses> courses = coursesIDao.queryWithPagingAndConditions("SELECT * FROM newcourseview", null, 1, 8, new BeanListHandler<>(Courses.class));
        //以方向为单位,返回课程信息
        for (Direction direction : directions) {
            String directionName = direction.getDirectionName();


            for (Courses cours : courses) {
                String courseSubjectName = cours.getCsubject();
                String courseDirectionName = cours.getCdirection();

                for (Subject subject : subjects) {

                    if (courseSubjectName.equals(subject.getSubjectName()) && !subject.getCourses().contains(cours)) {
                        subject.addCourses(cours);
                    }
                }
                if (courseDirectionName.equals(directionName)) {
                    for (Subject subject : subjects) {

                        if (courseSubjectName.equals(subject.getSubjectName()) && !direction.getSubjects().contains(subject)) {
                            direction.addSubjects(subject);
                        }
                    }
                }
            }
        }

        return directions;
    }

    /**
     * @desc  添加收藏课程
     * @param studentId
     * @param courseId
     * @return
     */
    public boolean addCourseToCollection(String studentId, String courseId) {
        if (studentId == null || courseId == null) {
            // 如果学生ID或者课程ID为null，直接返回添加失败
            return false;
        }
        // 可以在这里添加更多对studentId和courseId格式等合法性的校验逻辑，比如是否符合特定的ID格式要求等

        try {
            // 先查询是否已经存在相同的收藏记录
            String checkSql = "SELECT COUNT(*) FROM collect WHERE sid =? AND cid =?";
            Long count = coursesIDao.queryForObject(checkSql, new ScalarHandler<Long>(), studentId, courseId);
            if (count!= null && count > 0) {
                // 如果已经存在收藏记录，根据业务需求决定返回值，这里假设返回false表示不重复添加
                return false;
            }

            // 构建插入收藏记录的SQL语句
            String sql = "INSERT INTO collect (sid, cid) VALUES (?,?)";
            // 使用coursesIDao执行插入操作，传入学生ID和课程ID作为参数
            int affectedRows = coursesIDao.updateWithTransaction(sql, studentId, courseId);
            // 如果插入操作影响的行数大于0，表示插入成功，返回true；否则返回false
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * @desc  获取收藏的课程
     * @param studentId
     * @return
     */
    public List<Map<String, Object>> getCollectedCourses(String studentId) {
        try {
            // 构建查询SQL语句，通过collect表与courses表进行关联查询，获取收藏课程的相关信息
            String sql = "SELECT " +
                    "    c.cname, " +
                    "    c.cintroduce, " +
                    "    c.cprice, " +
                    "    c.cover " +
                    "FROM " +
                    "    collect sc " +
                    "JOIN " +
                    "    courses c ON sc.cid = c.id " +
                    "WHERE " +
                    "    sc.sid =?";
            // 使用coursesIDao执行查询操作，并返回结果（以Map形式表示每条课程记录）
            return coursesIDao.queryWithPagingAndConditions(sql, null, 1, 10, new MapListHandler(), studentId);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * @desc
     * @param pageSize
     * @param pageNo
     * @return
     * @throws SQLException
     */
    @Override
    public List<Courses> queryAll(int pageSize, int pageNo) throws SQLException {
        // 添加cstatus = '1'的筛选条件，确保只查询到状态为1的课程
        List<Courses> courses = coursesIDao.queryWithPagingAndConditions("SELECT * FROM courses WHERE cstatus = '1'", null, pageNo, pageSize, new BeanListHandler<>(Courses.class));
        return courses;
    }

    /**
     * @desc  查询全部条件查询课程  ( 免费的 和  付费的 )
     * @param condition
     * @param pageSize
     * @param pageNo
     * @return
     * @throws SQLException
     */
    public List<Courses> queryAll(String condition, int pageSize, int pageNo) throws SQLException {
        String sqlCondition = null;
        if ("free".equals(condition)) {
            sqlCondition = "cprice like " + "'0%'" + " AND cstatus = '1'";
        } else if ("paid".equals(condition)) {
            sqlCondition = "cstatus = '1' AND cprice not like" + "'0%'";
        }
        // 根据不同条件添加cstatus = '1'的筛选条件，确保查询结果符合要求
        List<Courses> courses = coursesIDao.queryWithPagingAndConditions("SELECT * FROM courses WHERE cstatus = '1'", sqlCondition, pageNo, pageSize, new BeanListHandler<>(Courses.class));
        return courses;
    }


    /**
     * @desc  根据id查询课程信息
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public Courses queryOneForId(String id) throws SQLException {
        return coursesIDao.queryForObject("SELECT * FROM courses WHERE id=?", new BeanHandler<Courses>(Courses.class), id);
    }

    /**
     * @desc  添加
     * @param obj
     * @throws SQLException
     */
    @Override
    public void add(Object... obj) throws SQLException {
        // 更新插入语句，添加cover字段
        coursesIDao.updateWithTransaction("INSERT INTO courses (id, tid, cover,csales, clevel, cname, cintroduce, cprice, cdirection, csubject, cviews, cstatus) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)", obj);
    }

    /**
     * @desc  更新
     * @param obj
     * @throws SQLException
     */
    @Override
    public void update(Object... obj) throws SQLException {
        coursesIDao.updateWithTransaction("INSERT INTO courses VALUES(?,?,?,?,?,?,?,?,?) WHERE id=?", obj);
    }

    /**
     * @desc  根据id删除课程信息
     * @param id
     * @throws SQLException
     */
    @Override
    public void deleteById(String id) throws SQLException {
        coursesIDao.updateWithTransaction("DELETE FROM courses WHERE id=?", id);
    }

    /**
     * @desc  结构化查询课程信息
     * @param condition
     * @param pageSize
     * @param pageNo
     * @return
     * @throws SQLException
     */
    public List<Direction> structurequery(String condition, int pageSize, int pageNo) throws SQLException {
        //获取查询条件
        String sqlCondition = null;
        if ("free".equals(condition)) {
            sqlCondition = "cprice like " + "'0%'";
        } else if ("paid".equals(condition)) {
            sqlCondition = "cprice not like" + "'0%'";
        } else if ("tfree".equals(condition)) {
            sqlCondition = "cprice like " + "'0%'" + " AND cstatus = " + "'1'";
        } else if ("tpaid".equals(condition)) {
            sqlCondition = "cprice not like" + "'0%'" + " AND cstatus = " + "'1'";
        } else if ("ffree".equals(condition)) {
            sqlCondition = "cprice  like" + "'0%'" + " AND cstatus = " + "'0'";
        } else if ("fpaid".equals(condition)) {
            sqlCondition = "cprice  not like" + "'0%'" + " AND cstatus =" + "'0'";
        }
        List<Direction> directions = coursesIDao.queryWithPagingAndConditions("SELECT DISTINCT cdirection AS directionName FROM courses", null, 1, 100, new BeanListHandler<>(Direction.class));
        List<Subject> subjects = coursesIDao.queryWithPagingAndConditions("SELECT DISTINCT csubject AS subjectName FROM courses", null, 1, 100, new BeanListHandler<>(Subject.class));
        List<Courses> courses = coursesIDao.queryWithPagingAndConditions("SELECT c.*,t.tname FROM courses c join teacher t on t.id = c.tid", sqlCondition, pageNo, 10000, new BeanListHandler<>(Courses.class));
        System.out.println(courses);
        //以方向为单位,返回课程信息
        for (Direction direction : directions) {
            String directionName = direction.getDirectionName();


            for (Courses cours : courses) {
                String courseSubjectName = cours.getCsubject();
                String courseDirectionName = cours.getCdirection();

                for (Subject subject : subjects) {

                    if (courseSubjectName.equals(subject.getSubjectName()) && !subject.getCourses().contains(cours)) {
                        subject.addCourses(cours);
                    }
                }

                if (courseDirectionName.equals(directionName)) {
                    for (Subject subject : subjects) {
                        if (courseSubjectName.equals(subject.getSubjectName()) && !direction.getSubjects().contains(subject)) {
                            direction.addSubjects(subject);
                        }
                    }
                }
            }
        }
        return directions;
    }

    public List<Courses> searchCoursesByName(String courseName) {
        try {
            // 构建模糊查询的SQL语句，使用LIKE关键字结合传入的课程名参数进行模糊匹配
            String sql = "SELECT * FROM courses WHERE cname LIKE ?";
            // 使用coursesIDao执行查询操作，传入课程名参数（已添加通配符），并将结果封装为Courses对象列表返回
            return coursesIDao.queryWithPagingAndConditions(sql, null, 1, 10000, new BeanListHandler<>(Courses.class), "%" + courseName + "%");
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}
